package com.cl.ks.flow;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.cl.ks.entity.SparkNode;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class GJ2SQL {
  static final Map<String, List<String>> sourceIdMap = new HashMap<>();
  static final Map<String, String > tableNameMap = new HashMap<>();
  static final Map<String,String> columnsMap = new HashMap<>();

  static final Map<String,List<String>> nodeIdColumnIdMap = new HashMap<>();


  public static void main(String[] args) throws IOException {
    String gj = FileUtils.readFileToString(new File("./gj3.json"), StandardCharsets.UTF_8);
    JSONObject gjObject = JSONObject.parseObject(gj);
    Map<String, String> sqlMap = new TreeMap<>();
    JSONArray nodes = gjObject.getJSONArray("nodes");

    for (int i = 0; i < nodes.size(); i++) {
      JSONObject node = nodes.getJSONObject(i);
      String nodeid = node.getString("nodeid");
      JSONObject properties = node.getJSONObject("properties");
      JSONArray tablecolumns = properties.getJSONArray("tablecolumns");
      for (int j = 0; j < tablecolumns.size(); j++) {
        JSONObject tablecolumn = tablecolumns.getJSONObject(j);
        String id = tablecolumn.getString("id");
        String columnid = tablecolumn.getString("columnid");
        columnsMap.put(id, columnid);
        nodeIdColumnIdMap.putIfAbsent(nodeid,new ArrayList<>());
        nodeIdColumnIdMap.get(nodeid).add(columnid);
        if (tablecolumn.containsKey("nodeid") && !tablecolumn.containsKey("sourceid")) {
          String idS = tablecolumn.getString("id");
          sourceIdMap.putIfAbsent(idS, new ArrayList<>());
          sourceIdMap.get(idS).add(nodeid);
        }
      }
    }
    for (int i = 0; i < nodes.size(); i++) {
      JSONObject node = nodes.getJSONObject(i);
      JSONObject properties = node.getJSONObject("properties");
      String nodeid = node.getString("nodeid");
      String type = node.getString("type");
      String sql = "";
      if ("DATAELEMENT".equals(type)) {
        sql = properties.getString("tablename");
      } else if ("DISTINCT".equals(type)) {
        sql = generateDistinctSQL(node);
      } else if ("DATAFILTER".equals(type)) {
        sql = generateDataFilterSQL(node);
      } else if ("JOIN".equals(type)) {
        sql = generateJoinSQL(node);
      } else if ("GROUPAGG".equals(type)) {
        sql = generateGroupSQL(node);
      } else if ("UNION".equals(type)) {
        sql = generateUnionSQL(node);
      } else if ("ADDCOLUMN".equals(type)) {
        sql = generateAddColumnSQL(node);
      } else if ("RESULT".equals(type)) {
        sql = generateResultSQL(node);
      }else if ("SQLEDITOR".equals(type)) {
        sql = properties.getString("sqlstring");
      }
      sqlMap.put(nodeid, sql);
      if (!sql.contains("SELECT") && !sql.contains("select")) {
        tableNameMap.put(nodeid,sql);
      }
    }

    Map<String, String> sqlQueries = new LinkedHashMap<>();

    JSONArray links = gjObject.getJSONArray("links");
    // 构建节点之间的连接
    for (int i = 0; i < links.size(); i++) {
      JSONObject link = links.getJSONObject(i);
      String from = (String) link.get("from");
      String to = (String) link.get("to");
      String sqlFrom = sqlMap.get(from);
      String sqlTo = sqlMap.get(to);
      if (sqlMap.containsKey(to)) {
        if (sqlTo.contains("#fromTable")) {
          sqlTo = sqlTo.replace("#fromTable", "TMPTABLE_" + from);
          sqlMap.put(to, sqlTo);
        }
      }


      sqlQueries.remove(from);
      sqlQueries.put(from, sqlFrom);
      sqlQueries.remove(to);
      sqlQueries.put(to, sqlTo);
    }
    List<String> sortedLinks = linksSort(links);

    // 生成最终的SQL脚本
    StringBuilder finalSQL = new StringBuilder();
    finalSQL.append("WITH\n");

    for (String key : sortedLinks) {

      String sql = sqlMap.get(key);

      // 替换临时表名
      for (Map.Entry<String, String> otherEntry : sqlQueries.entrySet()) {
        if (!otherEntry.getKey().equals(key)) {
          sql = sql.replace("TMPTABLE_" + otherEntry.getKey(), otherEntry.getKey());
        }
      }

      // 如果是表名，则不使用WITH子句
      if (sql.matches("[A-Za-z_][A-Za-z0-9_]*")) { // 检查是否为表名
        continue;
      }

      finalSQL.append(key).append(" AS (\n").append(sql).append("\n),\n");
    }

    // 移除最后一个逗号和换行符
    if (finalSQL.length() > 2) {
      finalSQL.setLength(finalSQL.length() - 2);
    }
    finalSQL.append(" select * from ").append(sortedLinks.get(sortedLinks.size() - 1));
    String sql = finalSQL.toString();

    for (Map.Entry<String, String> entry : tableNameMap.entrySet()) {
      String key = entry.getKey();
      String value = entry.getValue();
      sql = sql.replace(key,value);
    }

    System.out.println();
  }

  private static String generateAddColumnSQL(JSONObject node) {
    JSONObject properties =  node.getJSONObject("properties");
    List<String> columnList = new ArrayList<>();
    List<String> newColumnList = new ArrayList<>();
    JSONArray columns = properties.getJSONArray("columns");
    for (int i = 0; i < columns.size(); i++) {
      JSONObject column = columns.getJSONObject(i);
      String columnvalue = column.getString("columnvalue");
      String newcolumnid = column.getString("newcolumnid");
      columnList.add("'" + columnvalue + "' as " + newcolumnid);
      newColumnList.add(newcolumnid);
    }
    String columnsConvert = columnsConvert(node);
    String columnsStr =
        Arrays.stream(StringUtils.split(columnsConvert, ","))
            .filter(item -> !newColumnList.contains(item))
            .collect(Collectors.joining(","));
    return "SELECT " + columnsStr + "," + StringUtils.join(columnList, ",") + " FROM #fromTable";
  }

  private static String generateResultSQL(JSONObject node) {
    return "SELECT " + resultColumnsConvert(node) + " FROM #fromTable";
  }

  private static String generateDistinctSQL(JSONObject node) {
    String columns = distinctColumnsConvert(node);
    return "SELECT "
        + columnsConvert(node)
        + ",ROW_NUMBER() OVER (PARTITION BY " + columns +")"
        + " FROM #fromTable";
  }

  private static String generateDataFilterSQL(
      JSONObject node) {
    JSONObject properties = node.getJSONObject("properties");
    return "SELECT "
        + columnsConvert(node)
        + " from  #fromTable where "
        + buildWhereClause(properties);
  }

  private static String generateJoinSQL(JSONObject node) {
    String columns = joinColumnsConvert(node);
    return "SELECT " + columnsConvert(node) + " from  " + columns;
  }

  private static String joinColumnsConvert(JSONObject node) {
    JSONObject properties = node.getJSONObject("properties");
    JSONArray columns = properties.getJSONArray("columns");
    String leftnodeid = properties.getString("leftnodeid");
    String rightnodeid = properties.getString("rightnodeid");
    String joinSQL =
        "TMPTABLE_"
            + leftnodeid
            + " AS "
            + leftnodeid
            + " #jointType TMPTABLE_"
            + rightnodeid
            + " AS "
            + rightnodeid;
    String jointype = properties.getString("jointype");
    String mysqlJoinType;
    if ("JOIN_FULL".equals(jointype)) {
      mysqlJoinType = "join";
    } else if ("JOIN_INNER".equals(jointype)) {
      mysqlJoinType = "inner join";
    } else if ("JOIN_LEFT_EXCEPT".equals(jointype)) {
      mysqlJoinType = "left join";
    } else {
      throw new RuntimeException("jointype不存在: " + jointype);
    }
    joinSQL = joinSQL.replace("#jointType", mysqlJoinType);

    List<String> joinOnList = new ArrayList<>();
    for (int i = 0; i < columns.size(); i++) {
      JSONObject column = columns.getJSONObject(i);
      String leftid = column.getString("leftid");
      String rightid = column.getString("rightid");

      String leftColumn = columnsMap.get(leftid);
      String rightColumn = columnsMap.get(rightid);

      String joinOn =
          " on " + leftnodeid + "." + leftColumn + "=" + rightnodeid + "." + rightColumn;
      joinOnList.add(joinOn);
    }

    return joinSQL + StringUtils.join(joinOnList, " and ");
  }

  private static String distinctColumnsConvert(JSONObject node) {
    JSONObject properties = node.getJSONObject("properties");
    JSONArray columns = properties.getJSONArray("columns");
    JSONArray tablecolumns = properties.getJSONArray("tablecolumns");

    List<String> ids = new ArrayList<>();
    List<String> result = new ArrayList<>();
    for (int i = 0; i < columns.size(); i++) {
      JSONObject column = columns.getJSONObject(i);
      String id = column.getString("id");
      ids.add(id);
    }
    for (int i = 0; i < tablecolumns.size(); i++) {
      JSONObject tablecolumn = tablecolumns.getJSONObject(i);
      String id = tablecolumn.getString("id");
      String columnid = tablecolumn.getString("columnid");
      if (ids.contains(id)) {
        result.add(columnid);
      }
    }
    return StringUtils.join(result, ",");
  }

  private static String buildWhereClause(JSONObject properties) {
    if (properties == null || !properties.containsKey("conditions")) {
      return "";
    }

    JSONArray conditions = properties.getJSONArray("conditions");
    StringBuilder whereClause = new StringBuilder();
    String logicOperator = properties.getString("logicoperator");

    for (int i = 0; i < conditions.size(); i++) {
      JSONObject condition = conditions.getJSONObject(i);

      if (condition.containsKey("logicoperator")) {
        // 递归处理嵌套条件
        String nestedWhereClause = buildWhereClause(condition);
        if (!nestedWhereClause.isEmpty()) {
          if (whereClause.length() > 0) {
            whereClause.append(" ").append(logicOperator).append(" ");
          }
          whereClause.append("(").append(nestedWhereClause).append(")");
        }
      } else {
        // 处理单个条件
        String id = condition.getString("id");
        String column = columnsMap.get(id);
        String value = condition.getString("conditionvalue");
        String filterType = condition.getString("filtertype");

        if (whereClause.length() > 0) {
          whereClause.append(" ").append(logicOperator).append(" ");
        }
        whereClause.append(column).append(" ");
        if ("EQ".equals(filterType) && StringUtils.isNumeric(value)) {
          whereClause.append("=").append(value);
        } else if ("GT".equals(filterType)) {
          whereClause.append(">=").append(value);
        } else if ("LT".equals(filterType)) {
          whereClause.append("<=").append(value);
        } else if ("EQ".equals(filterType) && StringUtils.isNumeric(value)) {
          whereClause.append("='").append(value).append("'");
        } else if ("LIKE".equals(filterType)) {
          whereClause.append("like '%").append(value).append("%'");
        } else {
          throw new RuntimeException("filterType不存在:" + filterType);
        }
      }
    }

    return whereClause.toString();
  }

  public static String generateGroupSQL(JSONObject node) {
    JSONObject properties = node.getJSONObject("properties");
    // 提取分组字段
    JSONArray groups = properties.getJSONArray("groups");
    StringBuilder groupByClause = new StringBuilder();
    for (int i = 0; i < groups.size(); i++) {
      JSONObject group = groups.getJSONObject(i);
      String columnId = getColumnId(properties, group.getString("id"));
      if (i > 0) {
        groupByClause.append(", ");
      }
      groupByClause.append(columnId);
    }

    // 提取聚合函数
    JSONArray aggs = properties.getJSONArray("aggs");
    StringBuilder selectClause = new StringBuilder();
    for (int i = 0; i < aggs.size(); i++) {
      JSONObject agg = aggs.getJSONObject(i);
      String columnId = getColumnId(properties, agg.getString("id"));
      String aggregateType = agg.getString("aggregatetype");

      if (i > 0) {
        selectClause.append(", ");
      }
      selectClause
          .append(columnId)
          .append(",")
          .append(aggregateType)
          .append("(")
          .append(columnId)
          .append(") AS ")
          .append(aggregateType)
          .append("_")
          .append(columnId);
    }

    // 构建最终的SQL查询
    String sqlQuery =
        "SELECT "
            + selectClause
            + " FROM #fromTable "
            + " GROUP BY "
            + groupByClause;

    return sqlQuery;
  }

  private static String getColumnId(JSONObject properties, String id) {
    JSONArray tableColumns = properties.getJSONArray("tablecolumns");
    for (int i = 0; i < tableColumns.size(); i++) {
      JSONObject column = tableColumns.getJSONObject(i);
      if (column.getString("id").equals(id)) {
        return column.getString("columnid");
      }
    }
    return null;
  }

  public static String generateUnionSQL(JSONObject node) {
    JSONObject properties = node.getJSONObject("properties");
    // 提取列映射
    JSONArray columns = properties.getJSONArray("columns");
    JSONArray tablecolumns = properties.getJSONArray("tablecolumns");

    StringBuilder leftColumnMapping = new StringBuilder();
    StringBuilder rightColumnMapping = new StringBuilder();
    String leftnodeid = properties.getString("leftnodeid");
    String rightnodeid = properties.getString("rightnodeid");
    List<String> leftColumns = new ArrayList<>();
    List<String> rightColumns = new ArrayList<>();
    for (int i = 0; i < columns.size(); i++) {
      JSONObject column = columns.getJSONObject(i);
      String leftId = column.getString("leftid");
      String rightId = column.getString("rightid");

      if (i > 0) {
        leftColumnMapping.append(", ");
        rightColumnMapping.append(", ");
      }
      String leftColumnName = getColumnName(leftId, columnsMap);
      String rightColumnName = getColumnName(rightId, columnsMap);
      leftColumns.add(leftColumnName);
      rightColumns.add(rightColumnName);

      JSONObject tablecolumn = tablecolumns.getJSONObject(i);
      String columnid = tablecolumn.getString("columnid");
      leftColumnMapping.append(leftColumnName).append(" AS ").append(columnid);
      rightColumnMapping.append(rightColumnName).append(" AS ").append(columnid);
    }

    // 构建SQL查询
    String distinct = properties.getBoolean("distinct") ? "DISTINCT" : "ALL";
    String sqlQuery =
        "SELECT "
            + distinct
            + " "
            + leftColumnMapping
            + " FROM (SELECT "
            + StringUtils.join(leftColumns, ",")
            + " FROM TMPTABLE_"
            + leftnodeid
            + ") AS "
            + leftnodeid
            + " UNION "
            + distinct
            + " "
            + " SELECT "
            + rightColumnMapping
            + " FROM (SELECT "
            + StringUtils.join(rightColumns, ",")
            + " FROM TMPTABLE_"
            + rightnodeid
            + ") AS "
            + rightnodeid;

    return sqlQuery;
  }

  private static String getColumnName(String id, Map<String, String> columnsMap) {
    return columnsMap.get(id);
  }


  public static String resultColumnsConvert(JSONObject node) {
    Set<String> columnList = new LinkedHashSet<>(); // 使用 LinkedHashSet 保持插入顺序
    JSONObject properties = node.getJSONObject("properties");
    JSONArray tablecolumns = properties.getJSONArray("tablecolumns");
    for (int i = 0; i < tablecolumns.size(); i++) {
      JSONObject tablecolumn = tablecolumns.getJSONObject(i);
      String asName = tablecolumn.getString("columnid");
      String fieldName =  columnsMap.get(tablecolumn.getString("id"));
      columnList.add(fieldName + " AS " + asName);
    }
    return StringUtils.join(columnList,",");

  }
  public static String columnsConvert(JSONObject node) {
    JSONObject properties = node.getJSONObject("properties");
    Set<String> columnList = new LinkedHashSet<>(); // 使用 LinkedHashSet 保持插入顺序
    Set<String> columnExist = new HashSet<>();
    JSONArray tablecolumns = properties.getJSONArray("tablecolumns");

    for (int i = 0; i < tablecolumns.size(); i++) {
      JSONObject tablecolumn = tablecolumns.getJSONObject(i);
      String columnName = tablecolumn.getString("columnid");
      String sourceid = tablecolumn.getString("sourceid");

      // 检查列名是否已经存在
      if (columnExist.contains(columnName)) {
        // 如果已经存在，检查当前字段是否有前缀
        if (sourceid != null && !sourceid.isEmpty()) {
          List<String> sourceidMapping = sourceIdMap.get(sourceid);

          if (sourceidMapping != null && !sourceidMapping.isEmpty()) {
            String prefix = sourceidMapping.get(0);

            if (properties.containsKey("leftnodeid")) {
              String leftnodeid = properties.getString("leftnodeid");
              String rightnodeid = properties.getString("rightnodeid");

              if (sourceidMapping.contains(leftnodeid)) {
                prefix = leftnodeid;
              } else if (sourceidMapping.contains(rightnodeid)) {
                prefix = rightnodeid;
              }

              if (!prefix.equals(leftnodeid) && !prefix.equals( rightnodeid)) {
                List<String> leftNodeColumns = nodeIdColumnIdMap.get(leftnodeid);
                List<String> rightNodeColumns = nodeIdColumnIdMap.get(rightnodeid);
                if (leftNodeColumns.contains(columnName)) {
                  prefix = leftnodeid;
                } else if (rightNodeColumns.contains(columnName)) {
                  prefix = rightnodeid;
                }

              }
            }


            // 生成带前缀的列名
            String prefixedColumnName = prefix + "." + columnName + " AS " + columnName;

            // 替换已存在的列名
            columnList.remove(columnName);
            columnList.add(prefixedColumnName);
          }
        }
      } else {
        // 如果列名不存在
        if (sourceid != null && !sourceid.isEmpty()) {
          List<String> sourceidMapping = sourceIdMap.get(sourceid);

          if (sourceidMapping != null && !sourceidMapping.isEmpty()) {
            String prefix = sourceidMapping.get(0);

            if (properties.containsKey("leftnodeid")) {
              String leftnodeid = properties.getString("leftnodeid");
              String rightnodeid = properties.getString("rightnodeid");

              if (sourceidMapping.contains(leftnodeid)) {
                prefix = leftnodeid;
              } else if (sourceidMapping.contains(rightnodeid)) {
                prefix = rightnodeid;
              }
            }

            // 生成带前缀的列名
            columnName = prefix + "." + columnName + " AS " + columnName;
          }
        }

        // 添加到列列表和已存在集合
        columnList.add(columnName);
        columnExist.add(columnName);
      }
    }

    return StringUtils.join(columnList, ",");
  }

  private static List<String> linksSort(JSONArray links) {

    // 构建图和入度数组
    Map<String, List<String>> graph = new HashMap<>();
    Map<String, Integer> inDegree = new HashMap<>();

    for (int i = 0; i < links.size(); i++) {
      JSONObject link = links.getJSONObject(i);
      String from = link.getString("from");
      String to = link.getString("to");

      // 初始化图和入度
      graph.putIfAbsent(from, new ArrayList<>());
      graph.putIfAbsent(to, new ArrayList<>());
      inDegree.putIfAbsent(from, 0);
      inDegree.putIfAbsent(to, 0);

      // 更新图和入度
      graph.get(from).add(to);
      inDegree.put(to, inDegree.get(to) + 1);
    }

    // 拓扑排序
    List<String> result = new ArrayList<>();
    Queue<String> queue = new LinkedList<>();

    // 将所有入度为0的节点加入队列
    for (Map.Entry<String, Integer> entry : inDegree.entrySet()) {
      if (entry.getValue() == 0) {
        queue.offer(entry.getKey());
      }
    }

    while (!queue.isEmpty()) {
      String node = queue.poll();
      result.add(node);

      for (String neighbor : graph.get(node)) {
        inDegree.put(neighbor, inDegree.get(neighbor) - 1);
        if (inDegree.get(neighbor) == 0) {
          queue.offer(neighbor);
        }
      }
    }
    return result;
  }
}
